<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Object Identifier Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Data Types"
HREF="datatype.html"><LINK
REL="PREVIOUS"
TITLE="Composite Types"
HREF="rowtypes.html"><LINK
REL="NEXT"
TITLE="Pseudo-Types"
HREF="datatype-pseudo.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Composite Types"
HREF="rowtypes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Data Types</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Pseudo-Types"
HREF="datatype-pseudo.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="DATATYPE-OID"
>8.16. Object Identifier Types</A
></H1
><P
>    Object identifiers (OIDs) are used internally by
    <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> as primary keys for various
    system tables.  OIDs are not added to user-created tables, unless
    <TT
CLASS="LITERAL"
>WITH OIDS</TT
> is specified when the table is
    created, or the <A
HREF="runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS"
>default_with_oids</A
>
    configuration variable is enabled.  Type <TT
CLASS="TYPE"
>oid</TT
> represents
    an object identifier.  There are also several alias types for
    <TT
CLASS="TYPE"
>oid</TT
>: <TT
CLASS="TYPE"
>regproc</TT
>, <TT
CLASS="TYPE"
>regprocedure</TT
>,
    <TT
CLASS="TYPE"
>regoper</TT
>, <TT
CLASS="TYPE"
>regoperator</TT
>, <TT
CLASS="TYPE"
>regclass</TT
>,
    <TT
CLASS="TYPE"
>regtype</TT
>, <TT
CLASS="TYPE"
>regconfig</TT
>, and <TT
CLASS="TYPE"
>regdictionary</TT
>.
    <A
HREF="datatype-oid.html#DATATYPE-OID-TABLE"
>Table 8-23</A
> shows an overview.
   </P
><P
>    The <TT
CLASS="TYPE"
>oid</TT
> type is currently implemented as an unsigned
    four-byte integer.  Therefore, it is not large enough to provide
    database-wide uniqueness in large databases, or even in large
    individual tables.  So, using a user-created table's OID column as
    a primary key is discouraged.  OIDs are best used only for
    references to system tables.
   </P
><P
>    The <TT
CLASS="TYPE"
>oid</TT
> type itself has few operations beyond comparison.
    It can be cast to integer, however, and then manipulated using the
    standard integer operators.  (Beware of possible
    signed-versus-unsigned confusion if you do this.)
   </P
><P
>    The OID alias types have no operations of their own except
    for specialized input and output routines.  These routines are able
    to accept and display symbolic names for system objects, rather than
    the raw numeric value that type <TT
CLASS="TYPE"
>oid</TT
> would use.  The alias
    types allow simplified lookup of OID values for objects.  For example,
    to examine the <TT
CLASS="STRUCTNAME"
>pg_attribute</TT
> rows related to a table
    <TT
CLASS="LITERAL"
>mytable</TT
>, one could write:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;</PRE
><P>
    rather than:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM pg_attribute
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');</PRE
><P>
    While that doesn't look all that bad by itself, it's still oversimplified.
    A far more complicated sub-select would be needed to
    select the right OID if there are multiple tables named
    <TT
CLASS="LITERAL"
>mytable</TT
> in different schemas.
    The <TT
CLASS="TYPE"
>regclass</TT
> input converter handles the table lookup according
    to the schema path setting, and so it does the <SPAN
CLASS="QUOTE"
>"right thing"</SPAN
>
    automatically.  Similarly, casting a table's OID to
    <TT
CLASS="TYPE"
>regclass</TT
> is handy for symbolic display of a numeric OID.
   </P
><DIV
CLASS="TABLE"
><A
NAME="DATATYPE-OID-TABLE"
></A
><P
><B
>Table 8-23. Object Identifier Types</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Name</TH
><TH
>References</TH
><TH
>Description</TH
><TH
>Value Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="TYPE"
>oid</TT
></TD
><TD
>any</TD
><TD
>numeric object identifier</TD
><TD
><TT
CLASS="LITERAL"
>564182</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regproc</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_proc</TT
></TD
><TD
>function name</TD
><TD
><TT
CLASS="LITERAL"
>sum</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regprocedure</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_proc</TT
></TD
><TD
>function with argument types</TD
><TD
><TT
CLASS="LITERAL"
>sum(int4)</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regoper</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_operator</TT
></TD
><TD
>operator name</TD
><TD
><TT
CLASS="LITERAL"
>+</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regoperator</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_operator</TT
></TD
><TD
>operator with argument types</TD
><TD
><TT
CLASS="LITERAL"
>*(integer,integer)</TT
> or <TT
CLASS="LITERAL"
>-(NONE,integer)</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regclass</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_class</TT
></TD
><TD
>relation name</TD
><TD
><TT
CLASS="LITERAL"
>pg_type</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regtype</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_type</TT
></TD
><TD
>data type name</TD
><TD
><TT
CLASS="LITERAL"
>integer</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regconfig</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_ts_config</TT
></TD
><TD
>text search configuration</TD
><TD
><TT
CLASS="LITERAL"
>english</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>regdictionary</TT
></TD
><TD
><TT
CLASS="STRUCTNAME"
>pg_ts_dict</TT
></TD
><TD
>text search dictionary</TD
><TD
><TT
CLASS="LITERAL"
>simple</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>    All of the OID alias types accept schema-qualified names, and will
    display schema-qualified names on output if the object would not
    be found in the current search path without being qualified.
    The <TT
CLASS="TYPE"
>regproc</TT
> and <TT
CLASS="TYPE"
>regoper</TT
> alias types will only
    accept input names that are unique (not overloaded), so they are
    of limited use; for most uses <TT
CLASS="TYPE"
>regprocedure</TT
> or
    <TT
CLASS="TYPE"
>regoperator</TT
> are more appropriate.  For <TT
CLASS="TYPE"
>regoperator</TT
>,
    unary operators are identified by writing <TT
CLASS="LITERAL"
>NONE</TT
> for the unused
    operand.
   </P
><P
>    An additional property of the OID alias types is the creation of
    dependencies.  If a
    constant of one of these types appears in a stored expression
    (such as a column default expression or view), it creates a dependency
    on the referenced object.  For example, if a column has a default
    expression <TT
CLASS="LITERAL"
>nextval('my_seq'::regclass)</TT
>,
    <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
    understands that the default expression depends on the sequence
    <TT
CLASS="LITERAL"
>my_seq</TT
>; the system will not let the sequence be dropped
    without first removing the default expression.
   </P
><P
>    Another identifier type used by the system is <TT
CLASS="TYPE"
>xid</TT
>, or transaction
    (abbreviated <ABBR
CLASS="ABBREV"
>xact</ABBR
>) identifier.  This is the data type of the system columns
    <TT
CLASS="STRUCTFIELD"
>xmin</TT
> and <TT
CLASS="STRUCTFIELD"
>xmax</TT
>.  Transaction identifiers are 32-bit quantities.
   </P
><P
>    A third identifier type used by the system is <TT
CLASS="TYPE"
>cid</TT
>, or
    command identifier.  This is the data type of the system columns
    <TT
CLASS="STRUCTFIELD"
>cmin</TT
> and <TT
CLASS="STRUCTFIELD"
>cmax</TT
>. Command identifiers are also 32-bit quantities.
   </P
><P
>    A final identifier type used by the system is <TT
CLASS="TYPE"
>tid</TT
>, or tuple
    identifier (row identifier).  This is the data type of the system column
    <TT
CLASS="STRUCTFIELD"
>ctid</TT
>.  A tuple ID is a pair
    (block number, tuple index within block) that identifies the
    physical location of the row within its table.
   </P
><P
>    (The system columns are further explained in <A
HREF="ddl-system-columns.html"
>Section 5.4</A
>.)
   </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="rowtypes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype-pseudo.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Composite Types</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Pseudo-Types</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>